<?php

class Application_Model_PiloteDAO
{
	protected $_dbTable;

	public function setDbTable($dbTable)
	{
		if(is_string($dbTable))
		{
			$dbTable = new $dbTable();
		}
		if(!$dbTable instanceof Zend_Db_Table_Abstract)
		{
			throw new Exception('Invalid table data gateway provided');
		}
		$this->_dbTable = $dbTable;
		return $this;
	}
 
	public function getDbTable()
	{
		if(null === $this->_dbTable)
		{
			$this->setDbTable('Application_Model_DbTable_Pilote');
		}
		return $this->_dbTable;
	}
	
	public function save(Application_Model_Pilote $pilote)
	{
		$data = array(
			'PILNUM' => $pilote->getPilNum(),
			'PAYNUM' => $pilote->getPays()->getPayNum(),
			'PILNOM' => $pilote->getPilNom(),
			'PILPRENOM' => $pilote->getPilPrenom(),
			'PILDATENAIS' => $pilote->getPilDateNais(),
			'PILPIGISTE' => $pilote->getPilPigiste(),
			'PILPOINTS' => $pilote->getPilPoints(),
			'PILPOIDS' => $pilote->getPilPoids(),
			'PILTAILLE' => $pilote->getPilTaille(),
			'PILTEXTE' => $pilote->getPilTexte(),
			'ECUNUM' => $pilote->getEcurie()->getEcuNum()
		);
 
		if (null === ($pilNum = $pilote->getPilNum()))
		{
			unset($data['PILNUM']);
			$this->getDbTable()->insert($data);
        }
        else
        {
			$this->getDbTable()->update($data, array('PILNUM = ?' => $pilNum));
		}
		return true;
		//Sauvegarde Photos
		
		//Sauvegarde Sponsors
	}
	
	public function delete($id)
	{
		$essaisDbTable = new Application_Model_DbTable_Essais();
		$essaisDbTable->delete(array('PILNUM = ?' => $id));
		$courseDbTable = new Application_Model_DbTable_Course();
		$courseDbTable->delete(array('PILNUM = ?' => $id));
		$sponsoriseDbTable = new Application_Model_DbTable_Sponsorise();
		$sponsoriseDbTable->delete(array('PILNUM = ?' => $id));
		$photoDbTable = new Application_Model_DbTable_Photo();
		$photoDbTable->delete(array('PILNUM = ?' => $id));
		$this->getDbTable()->delete(array('PILNUM = ?' => $id));
	}
	
	public function find($pilNum)
	{
		$pilote = new Application_Model_Pilote();
		$photoDbTable = new Application_Model_DbTable_Photo();
		$ecurieDAO = new Application_Model_EcurieDAO();
		$paysDAO = new Application_Model_PaysDAO();
		$result = $this->getDbTable()->find($pilNum);
		if(0 == count($result))
		{
			return null;
		}
		$row = $result->current();
		$pilote->setPilNum($row->PILNUM)
			   ->setPilNom($row->PILNOM)
			   ->setPilPrenom($row->PILPRENOM)
			   ->setPilDateNais($row->PILDATENAIS)
			   ->setPilPigiste($row->PILPIGISTE)
			   ->setPilPoids($row->PILPOIDS)
			   ->setPilTaille($row->PILTAILLE)
			   ->setPilTexte($row->PILTEXTE);
		
		//On recupere l'écurie
		$ecurie = $ecurieDAO->find($row->ECUNUM);
		$pilote->setEcurie($ecurie);
		
		//On recupere le pays
		$pays = $paysDAO->find($row->PAYNUM);
		$pilote->setPays($pays);
		
		//On recupere les photos
		$resultSet = $photoDbTable->fetchAll($photoDbTable->select()->where("PILNUM = $pilNum")->order('PHONUM ASC'));
		$photos = array();
		foreach($resultSet as $row)
		{
			$photo = new Application_Model_Photo();
			$photo->setPhoNum($row->PHONUM)
				  ->setPilNum($row->PILNUM)
				  ->setPhoSujet($row->PHOSUJET)
				  ->setPhoCommentaire($row->PHOCOMMENTAIRE)
				  ->setPhoAdresse($row->PHOADRESSE);
			$photos[] = $photo;
		}
		if(count($photos)==0)
		{
			$photo=new Application_Model_Photo();
			$photo->setPhoAdresse('default.png');
			$photos[]=$photo;
		}
		
		$pilote->setPhotos($photos);
		
		//On recupere les sponsors
		$db = Zend_Db_Table::getDefaultAdapter();
		$resultSet = $db->query("select s.SPONUM, s.SPONOM, s.SPOSECTACTIVITE from sponsor s
									inner join sponsorise sp on s.SPONUM=sp.SPONUM
									where sp.PILNUM=$pilNum")->fetchAll(zend_db::FETCH_OBJ);
		$sponsors = array();
		foreach($resultSet as $row)
		{
			$sponsor = new Application_Model_Sponsor();
			$sponsor->setSpoNum($row->SPONUM)
					->setSpoNom($row->SPONOM)
					->setSpoSectActivite($row->SPOSECTACTIVITE);
			$sponsors[] = $sponsor;
		}
		$pilote->setSponsors($sponsors);
		
		//On calcule les points
		$points=$this->calculerPoints($pilNum);
		$pilote->setPilPoints($points);
		
		return $pilote;
	}
	
	public function findAll()
	{
		$resultSet = $this->getDbTable()->fetchAll($this->getDbTable()->select()->from('pilote',array('PILNUM'))->order('PILNOM ASC'));
		$entries = array();
		foreach($resultSet as $row)
		{
			$entries[] = $this->find((int)$row->PILNUM);
		}
		return $entries;
	}
	
	public function findByLettre($lettre)
	{
		$resultSet = $this->getDbTable()->fetchAll($this->getDbTable()->select()->from('pilote',array('PILNUM'))->where("PILNOM LIKE '$lettre%'")->order('PILNOM ASC'));
		$entries = array();
		foreach($resultSet as $row)
		{
			$entries[] = $this->find((int)$row->PILNUM);
		}
		return $entries;
	}
	
	public function findListeLettre()
	{
		$lettres = array();
		$lettre = "";
		$resultSet = $this->getDbTable()->fetchAll($this->getDbTable()->select()->order('PILNOM ASC'));
		foreach($resultSet as $row)
		{
			$l = strtolower(substr($row->PILNOM,0,1));
			if($l != $lettre)
			{
				$lettres[] = $l;
				$lettre = $l;
			}
		}
		return $lettres;
	}
	
	public function findByEcurie($ecuNum)
	{
		$resultSet = $this->getDbTable()->fetchAll($this->getDbTable()->select()->where('ECUNUM='.$ecuNum)->order('PILNOM ASC'));
		$entries = array();
		foreach($resultSet as $row)
		{
			$entries[] = $this->find((int)$row->PILNUM);
		}
		return $entries;
	}
	
	public function calculerPoints($id)
	{
		$points = 0;
		$db = Zend_Db_Table::getDefaultAdapter();
		$resultSet = $db->query("select GPNUM from course where PILNUM=$id")->fetchAll(zend_db::FETCH_OBJ);
		foreach($resultSet as $row)
		{
			$rs = $db->query("select PTNBPOINTSPLACE from (select @rownum:=@rownum+1 rank, c.* from course c, (SELECT @rownum:=0) r where c.GPNUM=".$row->GPNUM." order by TEMPSCOURSE ASC) classement left join points p on p.PTPLACE=rank where PILNUM=$id")->fetch(zend_db::FETCH_OBJ);
			if(is_null($pt=$rs->PTNBPOINTSPLACE))
			{
				$pt=0;
			}
			$points = $points+$pt;
		}
		return $points;	
	}
}